﻿using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal;
using Microsoft.EntityFrameworkCore.Storage;

namespace HMS.MultiTenant.SqlServer;

public sealed class SqlServerDynamicDatabaseCreator : SqlServerDatabaseCreator
{
    private readonly ISqlServerConnection _connection;
    private readonly IRawSqlCommandBuilder _rawSqlCommandBuilder;

    public SqlServerDynamicDatabaseCreator(
        RelationalDatabaseCreatorDependencies dependencies,
        ISqlServerConnection connection,
        IRawSqlCommandBuilder rawSqlCommandBuilder)
        : base(dependencies, connection, rawSqlCommandBuilder)
    {
        _connection = connection;
        _rawSqlCommandBuilder = rawSqlCommandBuilder;
    }

    public override bool HasTables()
        => Dependencies.ExecutionStrategy.Execute(
            _connection,
            connection => (int)CreateHasTablesCommand()
                    .ExecuteScalar(
                        new RelationalCommandParameterObject(
                            connection,
                            null,
                            null,
                            Dependencies.CurrentContext.Context,
                            Dependencies.CommandLogger, CommandSource.Migrations))!
                != 0,
            null);

    public override async Task<bool> HasTablesAsync(CancellationToken cancellationToken = default)
        => (int)(await Dependencies.ExecutionStrategy.ExecuteAsync(
                _connection,
                (connection, ct) => CreateHasTablesCommand()
                    .ExecuteScalarAsync(
                        new RelationalCommandParameterObject(
                            connection,
                            null,
                            null,
                            Dependencies.CurrentContext.Context,
                            Dependencies.CommandLogger, CommandSource.Migrations),
                        cancellationToken: ct),
                null,
                cancellationToken).ConfigureAwait(false))!
            != 0;

    private IRelationalCommand CreateHasTablesCommand()
    {
        var defaultSchema = Dependencies.CurrentContext.Context.GetService<IDesignTimeModel>().Model.GetDefaultSchema()!;

        var sql = $@"IF NOT EXISTS
	(SELECT *
	FROM [sys].[schemas] s
	WHERE [s].[name] = '{defaultSchema}'	
	)
SELECT 0 ELSE IF EXISTS
	(SELECT *
	FROM [sys].[objects] o
	WHERE [o].[schema_id]  = (SELECT [schema_id]
		FROM [sys].[schemas] s
		WHERE [s].[name] = '{defaultSchema}'	
		)	
	)
SELECT 1 ELSE SELECT 0";

        return _rawSqlCommandBuilder.Build(sql);
    }
}